# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:        黄周云  
# Name:         hmwk02
# Description:
# Author:       ASUS
# Date:         2019/4/20
#-------------------------------------------------------------------------------


"""
1上次作业json解析的结果写入mysql / sqlite3数据库中
2 1个表或者2个表 两个表要有关联关系
3 附上表结构, 要有创建表的sql语句
"""
import json
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INT,VARCHAR,create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Cp(Base):
    __tablename__ = "cp"
    id = Column(INT,primary_key=True)
    cpname = Column(VARCHAR(10))
    cp = Column(VARCHAR(10))


#不能使用常见的单词和关键字作为类名，Db_for_mysql
class Db_for_mysql:
    def __init__(self):
        self.pdDict = {}
        self.pdList = []
        self.listStr = []

    def create_window(self):
        engine = create_engine("mysql+mysqlconnector://root:980721@127.0.0.1:3306/chanpin?charset=utf8")
        Base.metadata.create_all(engine)
        Session_window = sessionmaker(engine)
        self.db_session = Session_window()

    def read_json_wirte2Db(self):
        try:
            f = open('products.json', 'r', encoding='utf-8')
            fr = f.read()
            #及时关闭文件
            f.close()
            distojb = json.loads(fr)
            dataList = distojb["data"]
            #用item1、item2更明确
            for item1 in dataList:
                name = item1["thd_name"]
                for item2 in item1["product"]:
                    title = item2["title"]
                    cp_ojb = Cp(cpname=name,cp=title)
                    self.db_session.add(cp_ojb)
            self.db_session.commit()
            self.db_session.close()
        #加一个回滚
        except Exception as e:
            print(e)
            self.db_session.rollback()



if __name__=="__main__":
    db1 = Db_for_mysql()
    db1.create_window()
    db1.read_json_wirte2Db()













